In [2]:
import pandas as pd
import os
import json
from datetime import datetime
def calculate_total_sum(filepaths):
summary = {}
grand_total = 0.0
for path in filepaths:
if not os.path.exists(path):
print(f"파일 없음: {path}")
continue
df = pd.read_excel(path)
if "총합 (MB)" not in df.columns:
print(f"'총합 (MB)' 컬럼 없음: {path}")
continue
try:
total_col = df["총합 (MB)"].fillna("").astype(str)
total_mb = total_col.apply(lambda x: float(x.replace(",", "")) if x else 0.0).sum()
file_key = os.path.basename(path)
summary[file_key] = round(total_mb, 3)
print(f"✔ {file_key}: {total_mb:,.3f} MB")
grand_total += total_mb
except Exception as e:
print(f"[ERROR] {path} 처리 중 오류: {e}")
summary["전체 총합 (MB)"] = round(grand_total, 3)
# JSON 저장
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
output_path = f"total_storage_summary_{timestamp}.json"
with open(output_path, "w", encoding="utf-8") as f:
json.dump(summary, f, indent=2, ensure_ascii=False)
print(f"\n전체 총합: {grand_total:,.3f} MB")
print(f"JSON 저장 완료: {os.path.abspath(output_path)}")
if __name__ == "__main__":
files = [
"db_datetime_size_summary.xlsx",
"db_not_size_summary.xlsx",
"db_empty_datetime_size_summary.xlsx"
]
calculate_total_sum(files)
✔ db_datetime_size_summary.xlsx: 1,583.900 MB ✔ db_not_size_summary.xlsx: 200.316 MB ✔ db_empty_datetime_size_summary.xlsx: 0.000 MB 전체 총합: 1,784.216 MB JSON 저장 완료: /Users/sc301/Desktop/yhy/project/lagacy_db/total_storage_summary_20250620_164626.json
엑셀 파일 하나로 합치기¶
In [3]:
import pandas as pd
file1 = "db_datetime_size_summary.xlsx"
file2 = "db_not_size_summary.xlsx"
file3 = "db_empty_datetime_size_summary.xlsx"
df1 = pd.read_excel(file1)
df2 = pd.read_excel(file2)
df3 = pd.read_excel(file3)
output_file = "combined_db_summary.xlsx"
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
df1.to_excel(writer, sheet_name="datetime 포함", index=False)
df2.to_excel(writer, sheet_name="용량 추정 불가", index=False)
df3.to_excel(writer, sheet_name="datetime 없음 추정", index=False)
print(f"엑셀 파일 저장 완료: {output_file}")
엑셀 파일 저장 완료: combined_db_summary.xlsx
JSON 파일 HTML 파일로 변환¶
In [4]:
import json
# 1. JSON 파일 경로
json_file = "DB 용량 통계.json"
html_file = "DB 용량 통계.html"
# 2. JSON 로드
with open(json_file, "r", encoding="utf-8") as f:
data = json.load(f)
# 3. HTML 초기 구성
html = """
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>DB 용량 요약</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
h2 { color: #333; margin-top: 40px; }
table { border-collapse: collapse; width: 100%; margin-bottom: 40px; }
th, td { border: 1px solid #ccc; padding: 8px; text-align: center; }
th { background-color: #f2f2f2; }
</style>
</head>
<body>
<h1>DB 사용량 요약</h1>
"""
# 4. 테이블 단위로 HTML 작성
for table_name, table_data in data.items():
html += f"<h2>{table_name}</h2>\n"
html += f"<p><strong>기간:</strong> {table_data.get('startDate')} ~ {table_data.get('endDate')}</p>\n"
# 연도별
year_data = table_data.get("year", {})
if year_data:
html += "<h3>연도별 사용량</h3>\n<table>\n<tr><th>연도</th><th>용량 (MB)</th></tr>\n"
for year, val in sorted(year_data.items()):
html += f"<tr><td>{year}</td><td>{val}</td></tr>\n"
html += "</table>\n"
# 월별
month_data = table_data.get("month", {})
if month_data:
html += "<h3>월별 사용량</h3>\n<table>\n<tr><th>월</th><th>용량 (MB)</th></tr>\n"
for month, val in sorted(month_data.items()):
html += f"<tr><td>{month}</td><td>{val}</td></tr>\n"
html += "</table>\n"
# 주차 데이터 생략 가능
html += "</body>\n</html>"
# 5. HTML 저장
with open(html_file, "w", encoding="utf-8") as f:
f.write(html)
print(f"HTML 파일로 변환 완료: {html_file}")
HTML 파일로 변환 완료: DB 용량 통계.html
JSON 파일로 저장¶
In [18]:
import pymysql
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from dateutil import parser
import json
import os
from dotenv import load_dotenv
# 날짜 상수
START_DATE_STATIC = datetime(2000, 1, 1)
END_DATE_STATIC = datetime.today()
# .env 파일 로드
load_dotenv()
# DB 연결
def get_connection():
host = os.getenv("DB_HOST")
user = os.getenv("DB_USER")
password = os.getenv("DB_PASSWORD")
db = os.getenv("DB_NAME")
port = int(os.getenv("DB_PORT", "3306"))
if not all([host, user, password, db]):
raise ValueError("필수 DB 환경 변수가 .env에 누락되어 있습니다.")
conn = pymysql.connect(
host=host,
user=user,
password=password,
db=db,
port=port,
charset='utf8',
connect_timeout=60,
read_timeout=300,
write_timeout=300
)
print(f"\nDB 연결 성공: {db}\n")
return conn, db
# 테이블 목록
def get_table_list(cursor, db_name):
cursor.execute(f"SHOW TABLES FROM `{db_name}`")
return [row[0] for row in cursor.fetchall()]
# datetime 컬럼 추출
def get_datetime_column(cursor, db_name, table):
cursor.execute("""
SELECT COLUMN_NAME, DATA_TYPE
FROM information_schema.columns
WHERE table_schema = %s AND table_name = %s
""", (db_name, table))
datetime_columns = [row[0] for row in cursor.fetchall() if row[1].lower() in ("datetime", "timestamp")]
for priority in ['reg_dt', 'created_at', 'insert_dt', 'log_time']:
if priority in datetime_columns:
return priority
return datetime_columns[0] if datetime_columns else None
# 유효한 날짜인지 검사
def is_valid_datetime(value):
return value not in ("0000-00-00", "0000-00-00 00:00:00", None, "")
# 날짜 범위 추출
def get_date_range(cursor, db_name, table, datetime_col):
try:
cursor.execute(f"""
SELECT MIN(`{datetime_col}`), MAX(`{datetime_col}`)
FROM `{db_name}`.`{table}`
WHERE `{datetime_col}` IS NOT NULL
AND `{datetime_col}` NOT IN ('0000-00-00', '0000-00-00 00:00:00')
AND `{datetime_col}` >= '1900-01-01' AND `{datetime_col}` < '2100-01-01'
""")
result = cursor.fetchone()
if not result or not is_valid_datetime(result[0]) or not is_valid_datetime(result[1]):
return None, None
return parser.parse(str(result[0])), parser.parse(str(result[1]))
except:
return None, None
# 평균 row 길이
def get_avg_row_length(cursor, db_name, table):
cursor.execute("""
SELECT AVG_ROW_LENGTH
FROM information_schema.tables
WHERE table_schema = %s AND table_name = %s
""", (db_name, table))
result = cursor.fetchone()
return result[0] if result and result[0] else 0
# row 수
def get_row_count(cursor, db_name, table):
cursor.execute(f"SELECT COUNT(*) FROM `{db_name}`.`{table}`")
return cursor.fetchone()[0]
# 단위별 날짜 범위
def date_range_by_unit(start, end, unit):
ranges = []
current = start
while current < end:
if unit == 'week':
next_point = current + timedelta(days=7)
label = f"{current:%Y-W%U}"
elif unit == 'month':
next_point = current + relativedelta(months=1)
label = f"{current:%Y-%m}"
elif unit == 'year':
next_point = current + relativedelta(years=1)
label = f"{current:%Y}"
else:
raise ValueError("단위 오류")
ranges.append((label, current, next_point))
current = next_point
return ranges
# 균등 분포 추정
def estimate_evenly_distributed_usage(row_count, avg_len, ranges):
total_mb = row_count * avg_len / 1024 / 1024
per_range_mb = total_mb / len(ranges) if ranges else 0
return {label: f"{per_range_mb:,.3f}" for label, _, _ in ranges}, total_mb
# 날짜 컬럼 기준 용량 추정
def estimate_storage(cursor, db_name, table, datetime_col, start, end, avg_len):
try:
cursor.execute(f"""
SELECT COUNT(*)
FROM `{db_name}`.`{table}`
WHERE `{datetime_col}` >= %s AND `{datetime_col}` < %s
AND `{datetime_col}` NOT IN ('0000-00-00', '0000-00-00 00:00:00')
""", (start, end))
count = cursor.fetchone()[0]
return round(count * avg_len / 1024 / 1024, 3)
except:
return 0
# 날짜 형식 포맷
def safe_date_format(date_obj):
return date_obj.strftime('%Y-%m-%d') if hasattr(date_obj, 'strftime') else str(date_obj)
# 메인 함수
def main():
conn, db_name = get_connection()
cursor = conn.cursor()
tables = get_table_list(cursor, db_name)
result = {
"총 테이블 수": len(tables),
"총 테이블 명 목록": tables,
"날짜 컬럼이 있는 테이블 수": 0,
"날짜 컬럼이 없는 테이블 수": 0,
"데이터가 없는 테이블 수": 0,
"평균 row length가 0인 테이블 수": 0,
"건너뛴 테이블 수": 0,
"건너뛴 테이블 목록": [],
"평균 row length가 0인 테이블 목록": [],
"날짜 컬럼이 있는 테이블 명 목록": [],
"날짜 컬럼이 없는 테이블 명 목록": [],
"데이터가 없는 테이블 목록": [],
"날짜 컬럼이 있는 테이블": {},
"날짜 컬럼이 없는 테이블": {},
"데이터가 없는 테이블": {},
}
# exclude_tables = ["md_conn_log_copy"] # 건너뛸 테이블 목록
exclude_tables = []
skipped_tables = [] # 건너뛴 테이블 저장용 리스트
for table in tables:
if table in exclude_tables:
print(f"건너뜀: {table}")
skipped_tables.append(table)
result["건너뛴 테이블 수"] += 1
continue
try:
print(f"분석 중: {table}")
row_count = get_row_count(cursor, db_name, table)
avg_len = get_avg_row_length(cursor, db_name, table)
if row_count == 0:
result["데이터가 없는 테이블 수"] += 1
result["데이터가 없는 테이블 목록"].append(table)
result["데이터가 없는 테이블"][table] = {
"startDate": None,
"endDate": None,
"week": {}, "month": {}, "year": {}
}
continue
if avg_len == 0:
result["평균 row length가 0인 테이블 수"] += 1
result["평균 row length가 0인 테이블 목록"].append(table)
continue
datetime_col = get_datetime_column(cursor, db_name, table)
if datetime_col:
start_date, end_date = get_date_range(cursor, db_name, table, datetime_col)
if not start_date or not end_date:
continue
result["날짜 컬럼이 있는 테이블 수"] += 1
result["날짜 컬럼이 있는 테이블 명 목록"].append(table)
table_result = {
"startDate": safe_date_format(start_date),
"endDate": safe_date_format(end_date),
"week": {}, "month": {}, "year": {}
}
for unit in ['week', 'month', 'year']:
for label, s, e in date_range_by_unit(start_date, end_date, unit):
mb = estimate_storage(cursor, db_name, table, datetime_col, s, e, avg_len)
if mb > 0:
table_result[unit][label] = f"{mb:,.3f}"
result["날짜 컬럼이 있는 테이블"][table] = table_result
else:
result["날짜 컬럼이 없는 테이블 수"] += 1
result["날짜 컬럼이 없는 테이블 명 목록"].append(table)
ranges = {
unit: date_range_by_unit(START_DATE_STATIC, END_DATE_STATIC, unit)
for unit in ['week', 'month', 'year']
}
table_result = {
"startDate": safe_date_format(START_DATE_STATIC),
"endDate": safe_date_format(END_DATE_STATIC),
"week": {}, "month": {}, "year": {}
}
for unit in ['week', 'month', 'year']:
usage, _ = estimate_evenly_distributed_usage(row_count, avg_len, ranges[unit])
table_result[unit] = usage
result["날짜 컬럼이 없는 테이블"][table] = table_result
except Exception as e:
print(f"오류 발생: {table} → {e}")
continue
# 건너뛴 테이블 목록 결과에 추가
result["건너뛴 테이블 목록"] = skipped_tables
now_str = datetime.now().strftime('%Y%m%d_%H%M%S')
json_file = f"db_storage_summary_{now_str}.json"
with open(json_file, "w", encoding="utf-8") as f:
json.dump(result, f, indent=2, ensure_ascii=False)
print("\n결과 요약:")
print(json.dumps(result, indent=2, ensure_ascii=False))
print(f"\nJSON 저장 위치: {os.path.abspath(json_file)}")
cursor.close()
conn.close()
if __name__ == "__main__":
main()
DB 연결 성공: meetu_db
분석 중: account
분석 중: admin
분석 중: advertisement
분석 중: application
분석 중: bookmark
분석 중: calendarEvent
분석 중: chatMessage
분석 중: chatRoom
분석 중: communityComment
분석 중: communityLike
분석 중: communityPost
분석 중: communityTag
분석 중: company
분석 중: companyBlock
분석 중: companyFollow
분석 중: coverLetter
분석 중: coverLetterContent
분석 중: coverLetterContentFeedback
분석 중: coverLetterContentFitAnalysis
분석 중: customerSupport
분석 중: interviewReview
분석 중: jobCategory
분석 중: jobPosting
분석 중: jobPostingJobCategory
분석 중: jobPostingViewLog
분석 중: location
분석 중: notification
분석 중: offer
분석 중: payment
분석 중: profile
분석 중: resume
분석 중: resumeContent
분석 중: resumeViewLog
분석 중: systemLog
결과 요약:
{
"총 테이블 수": 34,
"총 테이블 명 목록": [
"account",
"admin",
"advertisement",
"application",
"bookmark",
"calendarEvent",
"chatMessage",
"chatRoom",
"communityComment",
"communityLike",
"communityPost",
"communityTag",
"company",
"companyBlock",
"companyFollow",
"coverLetter",
"coverLetterContent",
"coverLetterContentFeedback",
"coverLetterContentFitAnalysis",
"customerSupport",
"interviewReview",
"jobCategory",
"jobPosting",
"jobPostingJobCategory",
"jobPostingViewLog",
"location",
"notification",
"offer",
"payment",
"profile",
"resume",
"resumeContent",
"resumeViewLog",
"systemLog"
],
"날짜 컬럼이 있는 테이블 수": 34,
"날짜 컬럼이 없는 테이블 수": 0,
"데이터가 없는 테이블 수": 0,
"평균 row length가 0인 테이블 수": 0,
"건너뛴 테이블 수": 0,
"건너뛴 테이블 목록": [],
"평균 row length가 0인 테이블 목록": [],
"날짜 컬럼이 있는 테이블 명 목록": [
"account",
"admin",
"advertisement",
"application",
"bookmark",
"calendarEvent",
"chatMessage",
"chatRoom",
"communityComment",
"communityLike",
"communityPost",
"communityTag",
"company",
"companyBlock",
"companyFollow",
"coverLetter",
"coverLetterContent",
"coverLetterContentFeedback",
"coverLetterContentFitAnalysis",
"customerSupport",
"interviewReview",
"jobCategory",
"jobPosting",
"jobPostingJobCategory",
"jobPostingViewLog",
"location",
"notification",
"offer",
"payment",
"profile",
"resume",
"resumeContent",
"resumeViewLog",
"systemLog"
],
"날짜 컬럼이 없는 테이블 명 목록": [],
"데이터가 없는 테이블 목록": [],
"날짜 컬럼이 있는 테이블": {
"account": {
"startDate": "2025-04-08",
"endDate": "2025-05-08",
"week": {
"2025-W14": "0.007",
"2025-W15": "0.003",
"2025-W16": "0.003",
"2025-W18": "0.002"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"admin": {
"startDate": "2025-04-08",
"endDate": "2025-04-08",
"week": {},
"month": {},
"year": {}
},
"advertisement": {
"startDate": "2025-04-17",
"endDate": "2025-04-27",
"week": {
"2025-W15": "0.015",
"2025-W16": "0.001"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"application": {
"startDate": "2025-04-08",
"endDate": "2025-05-26",
"week": {
"2025-W14": "0.003",
"2025-W15": "0.003",
"2025-W16": "0.003",
"2025-W20": "0.005"
},
"month": {
"2025-04": "0.010",
"2025-05": "0.005"
},
"year": {
"2025": "0.016"
}
},
"bookmark": {
"startDate": "2025-04-08",
"endDate": "2025-05-20",
"week": {
"2025-W14": "0.009",
"2025-W18": "0.003",
"2025-W20": "0.003"
},
"month": {
"2025-04": "0.009",
"2025-05": "0.006"
},
"year": {
"2025": "0.016"
}
},
"calendarEvent": {
"startDate": "2025-04-08",
"endDate": "2025-05-07",
"week": {
"2025-W14": "0.003",
"2025-W16": "0.013",
"2025-W17": "0.001",
"2025-W18": "0.001"
},
"month": {
"2025-04": "0.018"
},
"year": {
"2025": "0.018"
}
},
"chatMessage": {
"startDate": "2025-04-08",
"endDate": "2025-05-20",
"week": {
"2025-W14": "0.003",
"2025-W16": "0.001",
"2025-W18": "0.005",
"2025-W19": "0.006"
},
"month": {
"2025-04": "0.004",
"2025-05": "0.011"
},
"year": {
"2025": "0.016"
}
},
"chatRoom": {
"startDate": "2025-04-08",
"endDate": "2025-04-08",
"week": {},
"month": {},
"year": {}
},
"communityComment": {
"startDate": "2025-04-08",
"endDate": "2025-05-20",
"week": {
"2025-W14": "0.002",
"2025-W15": "0.012",
"2025-W16": "0.003"
},
"month": {
"2025-04": "0.017"
},
"year": {
"2025": "0.017"
}
},
"communityLike": {
"startDate": "2025-04-08",
"endDate": "2025-05-07",
"week": {
"2025-W14": "0.002",
"2025-W15": "0.007",
"2025-W16": "0.005",
"2025-W17": "0.001",
"2025-W18": "0.001"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"communityPost": {
"startDate": "2025-04-08",
"endDate": "2025-04-27",
"week": {
"2025-W14": "0.001",
"2025-W15": "0.013",
"2025-W16": "0.003"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"communityTag": {
"startDate": "2025-04-08",
"endDate": "2025-04-26",
"week": {
"2025-W14": "0.009",
"2025-W16": "0.007"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"company": {
"startDate": "2025-04-08",
"endDate": "2025-05-25",
"week": {
"2025-W14": "0.024",
"2025-W15": "0.038",
"2025-W16": "0.088",
"2025-W18": "0.003",
"2025-W20": "0.004"
},
"month": {
"2025-04": "0.152",
"2025-05": "0.004"
},
"year": {
"2025": "0.156"
}
},
"companyBlock": {
"startDate": "2025-04-08",
"endDate": "2025-05-02",
"week": {
"2025-W14": "0.007",
"2025-W16": "0.007",
"2025-W17": "0.002"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"companyFollow": {
"startDate": "2025-04-08",
"endDate": "2025-05-08",
"week": {
"2025-W14": "0.013",
"2025-W16": "0.003",
"2025-W18": "0.003"
},
"month": {
"2025-04": "0.016",
"2025-05": "0.003"
},
"year": {
"2025": "0.018"
}
},
"coverLetter": {
"startDate": "2025-04-08",
"endDate": "2025-04-27",
"week": {
"2025-W14": "0.007",
"2025-W16": "0.009"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"coverLetterContent": {
"startDate": "2025-04-08",
"endDate": "2025-04-27",
"week": {
"2025-W14": "0.006",
"2025-W16": "0.010"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"coverLetterContentFeedback": {
"startDate": "2025-04-08",
"endDate": "2025-04-08",
"week": {},
"month": {},
"year": {}
},
"coverLetterContentFitAnalysis": {
"startDate": "2025-04-19",
"endDate": "2025-04-27",
"week": {
"2025-W15": "0.007",
"2025-W16": "0.010"
},
"month": {
"2025-04": "0.017"
},
"year": {
"2025": "0.017"
}
},
"customerSupport": {
"startDate": "2025-04-08",
"endDate": "2025-04-08",
"week": {},
"month": {},
"year": {}
},
"interviewReview": {
"startDate": "2025-04-26",
"endDate": "2025-05-02",
"week": {
"2025-W16": "0.016"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"jobCategory": {
"startDate": "2025-04-08",
"endDate": "2025-05-25",
"week": {
"2025-W14": "0.010",
"2025-W20": "0.113"
},
"month": {
"2025-04": "0.010",
"2025-05": "0.113"
},
"year": {
"2025": "0.124"
}
},
"jobPosting": {
"startDate": "2025-04-08",
"endDate": "2025-05-25",
"week": {
"2025-W14": "0.045",
"2025-W15": "0.075",
"2025-W16": "0.282",
"2025-W18": "0.003",
"2025-W20": "0.018"
},
"month": {
"2025-04": "0.404",
"2025-05": "0.018"
},
"year": {
"2025": "0.422"
}
},
"jobPostingJobCategory": {
"startDate": "2025-04-08",
"endDate": "2025-05-25",
"week": {
"2025-W14": "0.001",
"2025-W18": "0.013",
"2025-W20": "0.001"
},
"month": {
"2025-04": "0.001",
"2025-05": "0.014"
},
"year": {
"2025": "0.016"
}
},
"jobPostingViewLog": {
"startDate": "2025-03-21",
"endDate": "2025-03-24",
"week": {
"2025-W11": "0.016"
},
"month": {
"2025-03": "0.016"
},
"year": {
"2025": "0.016"
}
},
"location": {
"startDate": "2025-04-08",
"endDate": "2025-04-22",
"week": {
"2025-W14": "0.062"
},
"month": {
"2025-04": "0.062"
},
"year": {
"2025": "0.062"
}
},
"notification": {
"startDate": "2025-04-08",
"endDate": "2025-05-20",
"week": {
"2025-W14": "0.001",
"2025-W16": "0.012",
"2025-W19": "0.002"
},
"month": {
"2025-04": "0.014",
"2025-05": "0.002"
},
"year": {
"2025": "0.016"
}
},
"offer": {
"startDate": "2025-04-08",
"endDate": "2025-04-08",
"week": {},
"month": {},
"year": {}
},
"payment": {
"startDate": "2025-04-08",
"endDate": "2025-04-27",
"week": {
"2025-W14": "0.001",
"2025-W15": "0.015",
"2025-W16": "0.001"
},
"month": {
"2025-04": "0.017"
},
"year": {
"2025": "0.017"
}
},
"profile": {
"startDate": "2025-04-08",
"endDate": "2025-04-28",
"week": {
"2025-W14": "0.007",
"2025-W15": "0.006",
"2025-W16": "0.002"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"resume": {
"startDate": "2025-04-08",
"endDate": "2025-04-28",
"week": {
"2025-W14": "0.002",
"2025-W15": "0.010",
"2025-W16": "0.003"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"resumeContent": {
"startDate": "2025-04-10",
"endDate": "2025-04-28",
"week": {
"2025-W14": "0.001",
"2025-W15": "0.006",
"2025-W16": "0.008"
},
"month": {
"2025-04": "0.016"
},
"year": {
"2025": "0.016"
}
},
"resumeViewLog": {
"startDate": "2025-04-08",
"endDate": "2025-04-08",
"week": {},
"month": {},
"year": {}
},
"systemLog": {
"startDate": "2025-04-08",
"endDate": "2025-05-31",
"week": {
"2025-W14": "0.001",
"2025-W16": "0.002",
"2025-W17": "0.001",
"2025-W18": "0.020",
"2025-W19": "0.013",
"2025-W20": "0.007",
"2025-W21": "0.001"
},
"month": {
"2025-04": "0.013",
"2025-05": "0.034"
},
"year": {
"2025": "0.047"
}
}
},
"날짜 컬럼이 없는 테이블": {},
"데이터가 없는 테이블": {}
}
JSON 저장 위치: d:\study\python\db_storage_summary_20250625_003654.json
추출되지 않는 테이블 명¶
In [3]:
import json
# JSON 파일 경로
file_path = 'db_storage_summary_20250624_111216.json'
# JSON 읽기
with open(file_path, 'r', encoding='utf-8') as f:
data = json.load(f)
# 키 목록 정의
part_keys = ["날짜 컬럼이 있는 테이블 명 목록", "날짜 컬럼이 없는 테이블 명 목록", "데이터가 없는 테이블 목록"]
total_key = "총 테이블 명 목록"
# 각 키의 개수 출력
print(f"\n테이블 목록 수")
for key in part_keys + [total_key]:
count = len(data.get(key, []))
print(f"- {key}: {count}개")
# 부분 키들의 값 합치기 (중복 제거)
combined_set = set()
for key in part_keys:
combined_set.update(data.get(key, []))
# 전체 목록 집합
total_set = set(data.get(total_key, []))
# 전체에 없는 값 (불일치 항목)
missing_values = combined_set - total_set
# 결과 출력
print(f"\n'총 테이블 명 목록'에 없는 값: {len(missing_values)}개")
print(missing_values)
테이블 목록 수 - 날짜 컬럼이 있는 테이블 명 목록: 159개 - 날짜 컬럼이 없는 테이블 명 목록: 26개 - 데이터가 없는 테이블 목록: 26개 - 총 테이블 명 목록: 215개 '총 테이블 명 목록'에 없는 값: 0개 set()
JSON 파일 -> 엑셀¶
In [17]:
import json
from datetime import datetime
import os
from openpyxl import Workbook
# 날짜 컬럼이 없는 경우 사용할 기준 시작일과 종료일을 설정
START_DATE_STATIC = datetime(2000, 1, 1)
END_DATE_STATIC = datetime.today()
# 분석 결과를 엑셀로 저장하는 함수
def save_excel(result_dict, filename):
print(f">>> EXCEl 파일 저장 중...")
wb = Workbook()
# Sheet 1: 요약
ws1 = wb.active
ws1.title = "요약"
for k, v in result_dict.items():
if isinstance(v, list) or isinstance(v, dict):
continue
ws1.append([k, v])
# 날짜 컬럼 있음 시트
ws2 = wb.create_sheet("날짜 컬럼 있음")
ws2.append(["테이블명", "월 단위 사용량", "년 단위 사용량", "연도 총합(MB)"])
total_col_with_date = 0
for table, data in result_dict["날짜 컬럼이 있는 테이블"].items():
year_sum = sum(float(v.replace(',', '')) for v in data["year"].values())
total_col_with_date += year_sum
ws2.append([table, len(data["month"]), len(data["year"]), f"{year_sum:,.3f}"])
ws2.append([])
ws2.append(["", "", "총합", f"{total_col_with_date:,.3f}"])
# 날짜 컬럼 없음 시트
ws3 = wb.create_sheet("날짜 컬럼 없음")
ws3.append(["테이블명", "월 단위 사용량", "년 단위 사용량", "연도 총합(MB)"])
total_col_without_date = 0
for table, data in result_dict["날짜 컬럼이 없는 테이블"].items():
year_sum = sum(float(v.replace(',', '')) for v in data["year"].values())
total_col_without_date += year_sum
ws3.append([table, len(data["month"]), len(data["year"]), f"{year_sum:,.3f}"])
ws3.append([])
ws3.append(["", "", "총합", f"{total_col_without_date:,.3f}"])
# 데이터 없음 시트
ws4 = wb.create_sheet("데이터 없음")
ws4.append(["테이블명"])
total_empty = 0
for table in result_dict["데이터가 없는 테이블 목록"]:
ws4.append([table])
total_empty += 1
ws4.append([])
ws4.append(["총 테이블 수", total_empty])
# Sheet 1에 각 시트 총합 요약 추가
ws1.append(["날짜 컬럼 있음 총합(MB)", f"{total_col_with_date:,.3f}"])
ws1.append(["날짜 컬럼 없음 총합(MB)", f"{total_col_without_date:,.3f}"])
ws1.append(["전체 총합(MB)", f"{total_col_with_date + total_col_without_date:,.3f}"])
wb.save(filename)
# 메인 함수
def main():
json_input_path = "db_storage_summary_20250625_002141.json"
if not os.path.exists(json_input_path):
print(f"파일이 존재하지 않음: {json_input_path}")
return
with open(json_input_path, "r", encoding="utf-8") as f:
result = json.load(f)
print("\n분석 결과 로드 완료.")
print("샘플 키 목록:", list(result.keys())[:5]) # 출력 축소 또는 생략
now_str = datetime.now().strftime('%Y%m%d_%H%M%S')
excel_file = f"db_storage_summary_{now_str}.xlsx"
save_excel(result, excel_file)
print(f"Excel 저장 완료: {os.path.abspath(excel_file)}")
if __name__ == "__main__":
main()
파일이 존재하지 않음: db_storage_summary_20250625_002141.json
JSON 파일 -> HTML¶
In [19]:
# 필요한 라이브러리 불러오기
import json # JSON 파일을 읽고 쓰기 위한 표준 라이브러리
from datetime import datetime # 현재 날짜와 시간을 가져오기 위한 라이브러리
import os # 파일 경로 및 존재 여부를 확인하기 위한 라이브러리
from IPython.display import display, HTML # Jupyter Notebook에서 HTML을 출력하기 위한 라이브러리
import plotly.graph_objs as go # Plotly에서 그래프(도형, 선 등)를 구성하기 위한 객체들
from plotly.offline import plot # HTML 안에 그래프를 그리기 위한 함수
# 각 테이블에서 연도별 데이터를 추출하는 함수
def extract_yearly_data_per_table(data_dict):
per_table_data = {} # 테이블별 결과를 저장할 딕셔너리
for table, data in data_dict.items(): # 테이블 이름과 해당 데이터 반복
year_data = {} # 연도별 데이터를 저장할 딕셔너리
for year, val in data["year"].items(): # 연도와 그 해의 값 반복
year_data[year] = float(val.replace(',', '')) # 쉼표 제거 후 숫자로 변환
per_table_data[table] = year_data # 테이블명에 연도별 데이터 저장
return per_table_data # 전체 테이블별 연도 데이터 반환
# HTML 파일을 생성하고 저장하는 함수
def save_html(result_dict, filename):
today_str = datetime.today().strftime('%Y-%m-%d') # 오늘 날짜를 'YYYY-MM-DD' 형식으로 저장
# JSON 데이터에서 날짜 컬럼이 있는 테이블과 없는 테이블의 연도별 데이터를 각각 추출
with_date_tables = extract_yearly_data_per_table(result_dict["날짜 컬럼이 있는 테이블"])
without_date_tables = extract_yearly_data_per_table(result_dict["날짜 컬럼이 없는 테이블"])
# 테이블 목록을 HTML 표 형태로 만드는 함수 (가로 방식: 테이블명을 열로, 값은 한 줄로 출력)
def make_table(title, table_dict):
headers = "".join(f"<th>{k}</th>" for k in table_dict.keys()) # 테이블명을 <th> 태그로 생성
values = "".join(f"<td>{v:.3f}</td>" for v in table_dict.values()) # 용량 값을 소수점 3자리까지 표현
return f"""
<details open>
<summary>{title} (총합: {sum(table_dict.values()):.3f} MB)</summary>
<table style='width:auto;'>
<tr>{headers}</tr>
<tr>{values}</tr>
</table>
</details>
"""
# 테이블 용량 요약 정보 계산 (합계)
total_with_date = sum(sum(tbl.values()) for tbl in with_date_tables.values()) # 날짜 컬럼 있는 테이블 총합
total_without_date = sum(sum(tbl.values()) for tbl in without_date_tables.values()) # 날짜 컬럼 없는 테이블 총합
# 각 테이블별로 연도 데이터를 모두 합쳐서 테이블 단위로 합계만 계산
table_with_date_dict = {k: sum(v.values()) for k, v in with_date_tables.items()}
table_without_date_dict = {k: sum(v.values()) for k, v in without_date_tables.items()}
table_empty_dict = {k: 0 for k in result_dict['데이터가 없는 테이블']} # 데이터가 아예 없는 테이블은 0MB로 처리
# 요약 정보를 담은 HTML 표 생성
summary_html = f"""
<details open>
<summary>1. 요약</summary>
<table>
<tr><th>항목</th><th>값</th></tr>
<tr><td>총 테이블 수</td><td>{result_dict['총 테이블 수']}</td></tr>
<tr><td>날짜 컬럼이 있는 테이블 수</td><td>{result_dict['날짜 컬럼이 있는 테이블 수']}</td></tr>
<tr><td>날짜 컬럼이 없는 테이블 수</td><td>{result_dict['날짜 컬럼이 없는 테이블 수']}</td></tr>
<tr><td>데이터가 없는 테이블 수</td><td>{result_dict['데이터가 없는 테이블 수']}</td></tr>
<tr><td>날짜 컬럼 있음 총합(MB)</td><td>{total_with_date:.3f}</td></tr>
<tr><td>날짜 컬럼 없음 총합(MB)</td><td>{total_without_date:.3f}</td></tr>
<tr><td>전체 총합(MB)</td><td>{total_with_date + total_without_date:.3f}</td></tr>
</table>
</details>
"""
# Plotly 그래프를 HTML 형식으로 생성하는 함수
def generate_plot_html(table_dict, title):
traces = [] # 그래프에 들어갈 데이터 라인을 담을 리스트
# 라인 색상을 구분하기 위한 컬러 팔레트
palette = ["#1f77b4", "#ff7f0e", "#2ca02c", "#d62728", "#9467bd",
"#8c564b", "#e377c2", "#7f7f7f", "#bcbd22", "#17becf"]
for idx, (table, year_map) in enumerate(table_dict.items()):
traces.append(go.Scatter( # 선 그래프(trace) 하나 생성
x=list(year_map.keys()), # x축: 연도
y=list(year_map.values()), # y축: 해당 연도 용량
mode='lines+markers', # 선과 점 모두 표시
name=table, # 범례에 표시할 이름
line=dict(color=palette[idx % len(palette)]) # 색상은 반복적으로 순환
))
fig = go.Figure(data=traces) # 그래프 생성
fig.update_layout(title=title, xaxis_title='연도', yaxis_title='용량 (MB)') # 그래프 제목과 축 라벨 설정
return plot(fig, include_plotlyjs='inline', output_type='div') # JS를 HTML 내부에 포함하여 그래프를 div로 반환
# 날짜 컬럼 있는 테이블용 그래프 HTML 생성
chart_with_date_html = generate_plot_html(with_date_tables, "테이블별 연도별 사용량 (날짜 컬럼 있음)")
# 날짜 컬럼 없는 테이블용 그래프 HTML 생성
chart_without_date_html = generate_plot_html(without_date_tables, "테이블별 연도별 사용량 (날짜 컬럼 없음)")
# 전체 HTML 페이지 구성
html = f"""
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>DB 테이블 용량 분석 결과</title>
<style>
body {{ font-family: Arial, sans-serif; padding: 20px; }}
summary {{ cursor: pointer; font-weight: bold; margin-top: 10px; }}
table {{ border-collapse: collapse; margin: 10px 0; }}
th, td {{ border: 1px solid #ccc; padding: 6px 10px; font-size: 13px; }}
div[id$='Chart'] {{ max-width: 100%; height: 400px; overflow-x: auto; }}
</style>
</head>
<body>
<h1>DB 테이블 용량 분석 결과 ({today_str})</h1>
{summary_html} <!-- 요약 정보 출력 -->
<details open><summary>2. 날짜 컬럼 있음 - 테이블별 연도별</summary>{chart_with_date_html}</details>
<details open><summary>3. 날짜 컬럼 없음 - 테이블별 연도별</summary>{chart_without_date_html}</details>
{make_table("4. 날짜 컬럼이 있는 테이블 명", table_with_date_dict)}
{make_table("5. 날짜 컬럼이 없는 테이블 명", table_without_date_dict)}
{make_table("6. 데이터가 없는 테이블 명", table_empty_dict)}
</body>
</html>
"""
# 위에서 생성한 HTML을 파일로 저장
with open(filename, "w", encoding="utf-8") as f:
f.write(html)
print(f"HTML 저장 완료: {os.path.abspath(filename)}") # 저장된 파일 경로 출력
# Jupyter Notebook에서 바로 확인할 수 있도록 HTML을 출력
display(HTML(html))
# 메인 함수: 전체 실행의 시작점
def main():
json_file = "db_storage_summary_20250625_003654.json" # 분석할 JSON 파일명
# 파일이 실제 존재하는지 확인
if not os.path.exists(json_file):
print(f"JSON 파일이 존재하지 않음: {json_file}")
return
# JSON 파일 읽기
with open(json_file, "r", encoding="utf-8") as f:
result = json.load(f)
# 현재 시각을 기준으로 HTML 파일 이름 생성
now_str = datetime.now().strftime('%Y%m%d_%H%M%S')
html_file = f"db_storage_summary_{now_str}.html"
# HTML 생성 및 출력 함수 실행
save_html(result, html_file)
# main() 함수를 직접 실행하도록 설정 (스크립트 단독 실행 시에만 동작)
if __name__ == "__main__":
main()
HTML 저장 완료: d:\study\python\db_storage_summary_20250625_003707.html
DB 테이블 용량 분석 결과 (2025-06-25)
1. 요약
| 항목 | 값 |
|---|---|
| 총 테이블 수 | 34 |
| 날짜 컬럼이 있는 테이블 수 | 34 |
| 날짜 컬럼이 없는 테이블 수 | 0 |
| 데이터가 없는 테이블 수 | 0 |
| 날짜 컬럼 있음 총합(MB) | 1.186 |
| 날짜 컬럼 없음 총합(MB) | 0.000 |
| 전체 총합(MB) | 1.186 |
2. 날짜 컬럼 있음 - 테이블별 연도별
3. 날짜 컬럼 없음 - 테이블별 연도별
4. 날짜 컬럼이 있는 테이블 명 (총합: 1.186 MB)
| account | admin | advertisement | application | bookmark | calendarEvent | chatMessage | chatRoom | communityComment | communityLike | communityPost | communityTag | company | companyBlock | companyFollow | coverLetter | coverLetterContent | coverLetterContentFeedback | coverLetterContentFitAnalysis | customerSupport | interviewReview | jobCategory | jobPosting | jobPostingJobCategory | jobPostingViewLog | location | notification | offer | payment | profile | resume | resumeContent | resumeViewLog | systemLog |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0.016 | 0.000 | 0.016 | 0.016 | 0.016 | 0.018 | 0.016 | 0.000 | 0.017 | 0.016 | 0.016 | 0.016 | 0.156 | 0.016 | 0.018 | 0.016 | 0.016 | 0.000 | 0.017 | 0.000 | 0.016 | 0.124 | 0.422 | 0.016 | 0.016 | 0.062 | 0.016 | 0.000 | 0.017 | 0.016 | 0.016 | 0.016 | 0.000 | 0.047 |
5. 날짜 컬럼이 없는 테이블 명 (총합: 0.000 MB)
6. 데이터가 없는 테이블 명 (총합: 0.000 MB)
특정 키 값을 엑셀로 저장¶
In [ ]:
import json
import pandas as pd
# 1. JSON 읽기
with open('db_storage_summary_20250624_111216.json', 'r', encoding='utf-8') as f:
json_data = json.load(f)
# 2. ',' 기준으로 분리
raw_list = json_data['총 테이블 명 목록']
rows = []
for line in raw_list:
parts = [part.strip() for part in line.split(',')]
for idx, name in enumerate(parts, start=1):
rows.append({'순번': idx, '테이블명': name})
# 3. 엑셀로 저장
df = pd.DataFrame(rows)
df.to_excel("output.xlsx", index=False)
# 결과값 출력
df
Out[ ]:
| 순번 | 테이블명 | |
|---|---|---|
| 0 | 1 | MDCL_RSV |
| 1 | 1 | SC_LOG |
| 2 | 1 | SC_TRAN |
| 3 | 1 | Surveys |
| 4 | 1 | _tiaraEvent |
| ... | ... | ... |
| 210 | 1 | user_token_use |
| 211 | 1 | yb_pormotion |
| 212 | 1 | yb_randing |
| 213 | 1 | zzAnswerB |
| 214 | 1 | zzEstimate |
215 rows × 2 columns